﻿using ClownFish;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using THL.DataAccess.Models;

namespace THL.DataAccess.MySQL
{
    public class DataProvider : BaseDataProvider, IDataProvider
    {
        public DataProvider() : base()
        {
            DbContext.RegisterDbConnectionInfo(base.DbName, base.ProviderName, "@", base.ConnectionString);
        }

        public List<StockRecord> TestConnection()
        {
            var record = DbHelper.FillList<StockRecord>("SELECT * FROM `thl_stock_stockrecord`;".AsCPQuery());
            //var record = DbHelper.GetDataItem<List<StockRecord>>("SELECT * FROM `thl_stock_stockrecord`;".AsCPQuery());
            return record;
        }

        public List<StockRecord> GetStockList()
        {
            var record = DbHelper.FillList<StockRecord>("SELECT * FROM `thl_stock_stockrecord`;".AsCPQuery());            
            return record;
        }

        public ZiJingLiuXiangRecord GetZJLX(Int32 stockId, DateTime jyrq)
        {
            ZiJingLiuXiangRecord record = DbHelper.GetDataItem<ZiJingLiuXiangRecord>(
                String.Format("SELECT * FROM `thl_stock_zijingliuxiangrecord` WHERE `Date`='{0}' AND `Stock_Id`={1} LIMIT 1;", jyrq, stockId).AsCPQuery());
            return record;
        }
        public HQRecord GetHQ(int p, DateTime dateTime)
        {
            HQRecord record = DbHelper.GetDataItem<HQRecord>(
                String.Format("SELECT `Id`,`Date`,`Stock_Id`,`DRSP`,`ZDF` FROM `thl_stock_quotationrecord` WHERE `Date`='{0}' AND `Stock_Id`={1} LIMIT 1;", dateTime, p).AsCPQuery());
            return record;
        }
        public Int32 CreateZJLX(ZiJingLiuXiangRecord newZJLX)
        {
            String sql = String.Format("INSERT INTO `orchard_develop`.`thl_stock_zijingliuxiangrecord` " + 
                "(`Stock_Id`, `Date`, `ZhuLi_JingLiuRu`, `ExtraLarge_JingLiuRu`, `Large_JingLiuRu`, `Medium_JingLiuRu`, `Small_JingLiuRu`, " + 
                //"`ZhuLi_JingLiuRu_Unit`, `ExtraLarge_JingLiuRu_Unit`, `Large_JingLiuRu_Unit`, `Medium_JingLiuRu_Unit`, `Small_JingLiuRu_Unit`, " +
                "`ZhuLi_JingZhanBi`, `ExtraLarge_JingZhanBi`, `Large_JingZhanBi`, `Medium_JingZhanBi`, `Small_JingZhanBi`, " + 
                "`OriginalData`) " +
                "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}');", 
                newZJLX.Stock_Id, newZJLX.Date, newZJLX.ZhuLi_JingLiuRu, newZJLX.ExtraLarge_JingLiuRu, newZJLX.Large_JingLiuRu, newZJLX.Medium_JingLiuRu, newZJLX.Small_JingLiuRu,
                //newZJLX.ZhuLi_JingLiuRu_Unit, newZJLX.ExtraLarge_JingLiuRu_Unit, newZJLX.Large_JingLiuRu_Unit, newZJLX.Medium_JingLiuRu_Unit, newZJLX.Small_JingLiuRu_Unit,
                newZJLX.ZhuLi_JingZhanBi, newZJLX.ExtraLarge_JingZhanBi, newZJLX.Large_JingZhanBi, newZJLX.Medium_JingZhanBi, newZJLX.Small_JingZhanBi,
                newZJLX.OriginalData);
            return DbHelper.ExecuteNonQuery(sql.AsCPQuery());
        }

        public Int32 UpdateZJLX(ZiJingLiuXiangRecord newZJLX, ZiJingLiuXiangRecord oldZJLX)
        {
            StringBuilder sb = new StringBuilder();
            String[] data = newZJLX.OriginalData.Split(',');
            newZJLX.OriginalData = String.Format(
                "{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15}",
                data[0], data[1], data[2], newZJLX.DRSP.ToString(), newZJLX.ZDF.ToString(),
                oldZJLX.ZhuLi_JingLiuRu == 0 ? newZJLX.ZhuLi_JingLiuRu.ToString() : oldZJLX.ZhuLi_JingLiuRu.ToString(),
                oldZJLX.ZhuLi_JingZhanBi == 0 ? newZJLX.ZhuLi_JingZhanBi.ToString() : oldZJLX.ZhuLi_JingZhanBi.ToString(),
                oldZJLX.ExtraLarge_JingLiuRu == 0 ? newZJLX.ExtraLarge_JingLiuRu.ToString() : oldZJLX.ExtraLarge_JingLiuRu.ToString(),
                oldZJLX.ExtraLarge_JingZhanBi == 0 ? newZJLX.ExtraLarge_JingZhanBi.ToString() : oldZJLX.ExtraLarge_JingZhanBi.ToString(),
                oldZJLX.Large_JingLiuRu == 0 ? newZJLX.Large_JingLiuRu.ToString() : oldZJLX.Large_JingLiuRu.ToString(),
                oldZJLX.Large_JingZhanBi == 0 ? newZJLX.Large_JingZhanBi.ToString() : oldZJLX.Large_JingZhanBi.ToString(),
                oldZJLX.Medium_JingLiuRu == 0 ? newZJLX.Medium_JingLiuRu.ToString() : oldZJLX.Medium_JingLiuRu.ToString(),
                oldZJLX.Medium_JingZhanBi == 0 ? newZJLX.Medium_JingZhanBi.ToString() : oldZJLX.Medium_JingZhanBi.ToString(),
                oldZJLX.Small_JingLiuRu == 0 ? newZJLX.Small_JingLiuRu.ToString() : oldZJLX.Small_JingLiuRu.ToString(),
                oldZJLX.Small_JingZhanBi == 0 ? newZJLX.Small_JingZhanBi.ToString() : oldZJLX.Small_JingZhanBi.ToString(),
                newZJLX.Date.ToString("yyyy-MM-dd 00:00:00"));
            sb.Append("UPDATE `orchard_develop`.`thl_stock_zijingliuxiangrecord` SET `Stock_Id` = '" + oldZJLX.Stock_Id + "' ");
            sb.Append(",`Date`='" + oldZJLX.Date + "' ");
            if (oldZJLX.ZhuLi_JingLiuRu == 0) sb.Append(",`ZhuLi_JingLiuRu`='" + newZJLX.ZhuLi_JingLiuRu + "' ");
            if (oldZJLX.ExtraLarge_JingLiuRu == 0) sb.Append(",`ExtraLarge_JingLiuRu`='" + newZJLX.ExtraLarge_JingLiuRu + "' ");
            if (oldZJLX.Large_JingLiuRu == 0) sb.Append(",`Large_JingLiuRu`='" + newZJLX.Large_JingLiuRu + "' ");
            if (oldZJLX.Medium_JingLiuRu == 0) sb.Append(",`Medium_JingLiuRu`='" + newZJLX.Medium_JingLiuRu + "' ");
            if (oldZJLX.Small_JingLiuRu == 0) sb.Append(",`Small_JingLiuRu`='" + newZJLX.Small_JingLiuRu + "' ");
            if (oldZJLX.ZhuLi_JingZhanBi == 0) sb.Append(",`ZhuLi_JingZhanBi`='" + newZJLX.ZhuLi_JingZhanBi + "' ");
            if (oldZJLX.ExtraLarge_JingZhanBi == 0) sb.Append(",`ExtraLarge_JingZhanBi`='" + newZJLX.ExtraLarge_JingZhanBi + "' ");
            if (oldZJLX.Large_JingZhanBi == 0) sb.Append(",`Large_JingZhanBi`='" + newZJLX.Large_JingZhanBi + "' ");
            if (oldZJLX.Medium_JingZhanBi == 0) sb.Append(",`Medium_JingZhanBi`='" + newZJLX.Medium_JingZhanBi + "' ");
            if (oldZJLX.Small_JingZhanBi == 0) sb.Append(",`Small_JingZhanBi`='" + newZJLX.Small_JingZhanBi + "' ");
            if (String.IsNullOrEmpty(oldZJLX.OriginalData)) sb.Append(",`OriginalData`='" + newZJLX.OriginalData + "' ");
            sb.Append("WHERE `thl_stock_zijingliuxiangrecord`.`Id` = " + oldZJLX.Id);
            return DbHelper.ExecuteNonQuery(sb.ToString().AsCPQuery());		
        }


        public Int32 CreateHQ(HQRecord hQRecord)
        {
            String sql = String.Format("INSERT INTO `orchard_develop`.`thl_stock_quotationrecord` " +
                "(`Stock_Id`, `Date`, `ZDF`, `DRSP`) " +
                "VALUES ('{0}', '{1}', '{2}', '{3}');",
                hQRecord.Stock_Id, hQRecord.Date, hQRecord.ZDF, hQRecord.DRSP);
            return DbHelper.ExecuteNonQuery(sql.AsCPQuery());
        }

        public Int32 UpdateHQ(HQRecord hQRecord)
        {
            String sql = String.Format("UPDATE `orchard_develop`.`thl_stock_quotationrecord` SET " +
                "`DRSP` = '{0}'," +
                "`ZDF` = '{1}'," +
                "`Stock_Id` = '{2}'," +
                "`Date` = '{3}' " + "WHERE `thl_stock_quotationrecord`.`Id` = {4}",
                hQRecord.DRSP, hQRecord.ZDF, hQRecord.Stock_Id, hQRecord.Date, hQRecord.Id);
            return DbHelper.ExecuteNonQuery(sql.AsCPQuery());
        }
    }
}
